﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Ex6.ExtractProductsInCategories
{
    public class ExtractProducts
    {
        private static string connectionString = "Server=.\\SQLEXPRESS; " +
            "Database=Northwind; Integrated Security=true";

        public static void Main()
        {
            SqlConnection dbCon = new SqlConnection(connectionString);
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand getProductsAndCategories = new SqlCommand(
                    @"SELECT c.CategoryName,p.ProductName FROM Categories c 
                      INNER JOIN Products p 
                      ON c.CategoryID = p.CategoryID 
                      ORDER BY c.CategoryName ",
                    dbCon);
                SqlDataReader reader = getProductsAndCategories.ExecuteReader();
                Dictionary<string, List<string>> dict = new Dictionary<string, List<string>>();
                using (reader)
                {
                    while (reader.Read())
                    {
                        string categoryName = (string)reader["CategoryName"];
                        string productName = (string)reader["ProductName"];
                        if (!dict.ContainsKey(categoryName))
                        {
                            dict.Add(categoryName, new List<string>());
                        }
                        dict[categoryName].Add(productName);
                    }
                }
                PrintResult(dict);
            }
        }

        private static void PrintResult(Dictionary<string, List<string>> dict)
        {
            foreach (string cat in dict.Keys)
            {
                Console.WriteLine("\nCategory {0}:\n", cat);
                foreach (string product in dict[cat])
                {
                    Console.WriteLine("Product - {0}", product);
                }
            }
        }
    }
}
